package com.lwj.hiring.company.dao;

import java.util.ArrayList;
import java.util.List;

import com.lwj.hiring.common.util.DBUtil;
import com.lwj.hiring.company.entity.ApplicationEntity;

/**
 * ApplicationDao
 * 
 * @author Administrator
 *
 */
public class ApplicationDao {

	/**
	 * 查询某个求职者所有求职信息
	 * 
	 * @param talentId
	 * @return
	 */
	public List<ApplicationEntity> list(int talentId) {
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT ");
		sb.append("r.intention AS 'resume.intention', ");
		sb.append("p.name as 'position.name', ");
		sb.append("c.name as 'company.name', ");
		sb.append("a.id, ");
		sb.append("a.app_time, ");
		sb.append("a.`status`, ");
		sb.append("a.handle_time ");
		sb.append("FROM application a, resume r, position p, company c ");
		sb.append("WHERE a.resume_id = r.id AND a.position_id = p.id AND p.company_id = c.id ");
		sb.append("AND a.talent_id = ? ");
		sb.append("ORDER BY id DESC ");
		List<ApplicationEntity> applicationList = DBUtil.executeQuery(
				ApplicationEntity.class, sb.toString(), talentId);
		return applicationList;
	}

	/**
	 * 撤销职位申请
	 * 
	 * @return
	 */
	public int delete(int id) {
		String sql = "DELETE FROM application WHERE id=?";
		return DBUtil.executeUpdate(sql, id);
	}

	/**
	 * 验证是否已投递简历
	 * 
	 * @param talentId
	 * @param id
	 * @return
	 */
	public ApplicationEntity isApply(int talentId, int id) {
		String sql = "SELECT * FROM application WHERE talent_id=? AND position_id=?";
		return DBUtil.getUniqueResult(ApplicationEntity.class, sql, talentId,
				id);
	}

	/**
	 * 求职者的简历投递
	 * 
	 * @param talentId
	 * @param resumeId
	 * @param positionId
	 * @return
	 * @throws RuntimeException
	 */
	public int add(int talentId, int resumeId, int positionId) {
		String sql = "INSERT INTO application VALUES(NULL, ?, ?, ?, 1, NOW(), NULL);";
		return DBUtil.executeUpdate(sql, positionId, talentId, resumeId);
	}

	public List<ApplicationEntity> applyList(int id) {
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT ");
		sb.append("p.name as 'position.name', ");
		sb.append("t.id as 'talent_id', ");
		sb.append("t.name as 'talent.name', ");
		sb.append("r.id as 'resume_id', ");
		sb.append("r.intention AS 'resume.intention', ");
		sb.append("a.id, ");
		sb.append("a.app_time, ");
		sb.append("a.`status`, ");
		sb.append("a.handle_time ");
		sb.append("FROM application a, resume r, position p, talent t ");
		sb.append("WHERE a.resume_id = r.id AND a.position_id = p.id AND a.talent_id = t.id ");
		sb.append("AND p.company_id = ? ");
		sb.append("ORDER BY id DESC ");
		List<ApplicationEntity> applicationList = DBUtil.executeQuery(
				ApplicationEntity.class, sb.toString(), id);
		return applicationList;
	}

	/**
	 * 审核
	 * 
	 * @param status
	 * @param applicationId
	 * @return
	 */
	public int verify(int status, int applicationId) {
		String sql = "UPDATE application SET status=?,handle_time=NOW() WHERE id=?";
		return DBUtil.executeUpdate(sql, status, applicationId);
	}

	/**
	 * 按条件查询申请职位信息
	 * 
	 * @param companyId
	 * @param status
	 * @param positionName
	 * @return
	 */
	public List<ApplicationEntity> listCompanyVerify(int companyId, int status,
			String positionName) {
		List<Object> paramList = new ArrayList<>();
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT ");
		sb.append("p.name as 'position.name', ");
		sb.append("t.id as 'talent_id', ");
		sb.append("t.name as 'talent.name', ");
		sb.append("r.id as 'resume_id', ");
		sb.append("r.intention AS 'resume.intention', ");
		sb.append("a.id, ");
		sb.append("a.app_time, ");
		sb.append("a.`status`, ");
		sb.append("a.handle_time ");
		sb.append("FROM application a, resume r, position p, talent t ");
		sb.append("WHERE a.resume_id = r.id AND a.position_id = p.id AND a.talent_id = t.id ");
		sb.append("AND p.company_id = ? ");
		paramList.add(companyId);
		// 职位名称
		if (!"".equals(positionName)) {
			sb.append("AND p.name LIKE ? ");
			paramList.add("%" + positionName + "%");
		}
		// 审核状态
		if (status != 0) {
			sb.append("AND a.status = ? ");
			paramList.add(status);
		}
		sb.append("ORDER BY id DESC ");
		return DBUtil.executeQuery(ApplicationEntity.class, sb.toString(),
				paramList);
	}
}
